<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Revenue Generated Report</title>
	<?php
		include('Connection.php');
	?>
</head>

<body>

	<h1 align="center">Revenue Generated</h1><br />
    <h2 align="center">Report for last 3 months</h2><br />
    
    <table width="100%" border="1px" align="center">
    	<tr>
        	<td>Vehicle Sno</td>
            <td>Type</td>
            <td>Car Model</td>
            <td>Reservation Revenue</td>
            <td>Late Fees Revenue</td>
        </tr>
        
        <?php
			//query database for all cars
			$query = "SELECT(R.VehicleSno, C.Type, C.ModelName, SUM(R.EstimatedCost) AS Revenue,
					  SUM(R.LateFees) AS TotalLateFees
					 
					  FROM CAR C, RESERVATION R
				     
					  WHERE R.ReturnDate BETWEEN (CURRENT_DATE() AND (CURRENT_DATE() - INTERVAL 3 MONTH) AND          		                      R.ReturnStatus = true AND C.VehicleSno = R.VehicleSno
				      
					  GROUP BY C.ModelName;";	
					  								
			$result = mysql_query($query) or die(mysql_error());
										
			for($i = 0; $i < mysql_num_rows($result); $i++){
				
				echo '<tr>';
				
				for ($j = 0; $j < 5; $j++){
					
					if(j == 0)
 						echo '<td>'.$row[VehicleSno].'</td>';
						
					else if(j == 1)
 						echo '<td>'.$row[Type].'</td>';
						
					else if(j == 2)
 						echo '<td>'.$row[CarModel].'</td>';
						
					else if(j == 3)
 						echo '<td>'.$row[Type].'</td>';
						
					else
 						echo '<td>'.$row[TotalLateFees].'</td>';			
 				 }
				 
				echo '</tr>';
			}
		?>
    			
        
    </table>
    

</body>
</html>